package com.util.excel.common;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

/**
 * 解析excel工具类
 * @date 2018年10月23日
 * @author W-L
 * 
 */
public class ParseExcelUtil {
	
	/**
	 * 一行一行解析excel
	 * @param sheet
	 * @param startRowNum  开始行行号
	 * @param startColNum  开始列列号
	 * @param endColNum    结束列列号
	 * @return
	 */
	public static List<Map<String,String>> parseSheetRowByRow(Sheet sheet,int startRowNum,int startColNum,int endColNum){
		List<Map<String,String>> rowList = new ArrayList<Map<String,String>>();
		//得到有效行数
		int rowNums = sheet.getLastRowNum();
		//第一行为列名称
		Row firstRow = sheet.getRow(0);
		//遍历从第二行开始
		for(int rowIndex = startRowNum + 1; rowIndex <= rowNums - 1; rowIndex++) {
			Row row = sheet.getRow(rowIndex);
			//如果首列（id列）为空则视为垃圾数据
			/*if(row == null || row.getCell(rowIndex) == null ) {
				break;
			}*/
			Map<String,String> cells = new HashMap<String, String>();
			//得到有效列数
			short cellNums = row.getLastCellNum();
			if(endColNum < 0) 
				endColNum = cellNums;
			//遍历
			for(int cellIndex = startColNum; cellIndex <= endColNum - 1; cellIndex++) {
				Cell cell = row.getCell(cellIndex);
				Cell firstRowCell = firstRow.getCell(cellIndex);
				String colName = "";
				String colVal = "";
				//列名称存入key
				if(firstRowCell != null) {
					firstRowCell.setCellType(Cell.CELL_TYPE_STRING);
					colName = firstRowCell.getStringCellValue().trim();
				}
				if(cell != null ) {
					cell.setCellType(Cell.CELL_TYPE_STRING);
					colVal = cell.getStringCellValue().trim();
				}
				cells.put(colName, colVal);
			}
			rowList.add(cells);
		}
		return rowList;
	}
	
	public static void main(String[] args) throws Exception {
		String path = "C:\\Users\\Administrator\\Desktop\\资产要素表\\未取到科目.xls";
		HSSFWorkbook book = new HSSFWorkbook(new FileInputStream(new File(path)));
		HSSFSheet sheet = book.getSheetAt(0);
		List<Map<String, String>> list = parseSheetRowByRow(sheet, 0, 1, 2);
		for (Map<String, String> map : list) {
			Set<String> keySet = map.keySet();
			for (String key : keySet) {
				System.out.println(key + ":" + map.get(key));
			}
		}
		
		
	/*	Properties prop = new Properties();
		prop.load(new FileInputStream(new File("config/db.properties")));
		String path = prop.getProperty("excelpath");
		System.out.println(path);*/
	}
		
}




